package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Customer;
import cn.gson.crm.model.entity.Degree;
import cn.gson.crm.model.entity.Status;
import cn.gson.crm.model.entity.Way;
import cn.gson.crm.vo.CustomerVo;

public class CustomerDao {
	/**
	 * 根据登录的人查询是否有需要跟进的学员
	 * @param empId
	 * @return
	 * @throws SQLException 
	 */
	public List<CustomerVo> getAllCustomer(int empId,int status) throws SQLException{
		String sql = "select cust_id,cust_name,cust_gender,cust_address,cust_telphone,cust_degree_id,degree_name,cust_qq,cust_parentTel,cust_parentname,cust_hobby,cust_source,cust_counselor_id,emp_name,cust_createtime,cust_drividetime,cust_status_id,status_name from customer c " +
						"inner join employee e on c.cust_counselor_id = e.emp_id " +
						"inner join degree d on d.degree_id = c.cust_degree_id " +
						"inner join `status` s on s.status_id = c.cust_status_id " +
						"where c.cust_counselor_id = ? and cust_status_id = ?";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, empId,status);
		List<CustomerVo> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			CustomerVo cv = new CustomerVo();
			cv.setCust_id(Integer.parseInt(mm.get("cust_id").toString()));
			cv.setCust_degree_id(Integer.parseInt(mm.get("cust_degree_id").toString()));
			cv.setCust_counselor_id(Integer.parseInt(mm.get("cust_counselor_id").toString()));
			cv.setCust_status_id(Integer.parseInt(mm.get("cust_status_id").toString()));
			cv.setCust_name((String)mm.get("cust_name"));
			cv.setCust_gender((String)mm.get("cust_gender"));
			cv.setCust_address((String)mm.get("cust_address"));
			cv.setCust_telphone((String)mm.get("cust_telphone"));
			cv.setDegree_name((String)mm.get("degree_name"));
			cv.setCust_qq((String)mm.get("cust_qq"));
			cv.setCust_parentTel((String)mm.get("cust_parentTel"));
			cv.setCust_parentName((String)mm.get("cust_parentname"));
			cv.setCust_hobby((String)mm.get("cust_hobby"));
			cv.setCust_source((String)mm.get("cust_source"));
			cv.setEmp_name((String)mm.get("emp_name"));
			cv.setCust_createtime((Timestamp)mm.get("cust_createtime"));
			cv.setCust_drividetime((Timestamp)mm.get("cust_drividetime"));
			cv.setStatus_name((String)mm.get("status_name"));
			
			list.add(cv);
		}
		return list;
	}
	
	/**
	 * 修改客户信息
	 * @param c
	 * @return
	 * @throws SQLException
	 */
	public boolean updateCustomer(Customer c) throws SQLException{
		String sql = "update customer set cust_name = ?,cust_address = ?,cust_gender = ?,cust_degree_id = ?,cust_telphone = ?,cust_qq = ?,cust_parentTel = ?, " +
						"cust_parentname = ?,cust_source = ? " +
							"where cust_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, c.getCust_name(),c.getCust_address(),c.getCust_gender(),c.getCust_degree_id(),c.getCust_telphone(),c.getCust_qq(),
					c.getCust_parentTel(),c.getCust_parentName(),c.getCust_source(),c.getCust_id());
	}
	
	/**
	 * 获取所有学历
	 * @return
	 * @throws SQLException
	 */
	public List<Degree> getAllDegree() throws SQLException{
		String sql = "select * from degree";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<Degree> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			Degree degree = new Degree(
					Integer.parseInt(mm.get("degree_id").toString()),
					(String)mm.get("degree_name")
					);
			
			list.add(degree);
		}
		return list;
	}
	
	/**
	 * 获取所有状态
	 * @return
	 * @throws SQLException
	 */
	public List<Status> getAllStatus() throws SQLException{
		String sql = "select * from `status`";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<Status> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			Status status = new Status(
					Integer.parseInt(mm.get("status_id").toString()),
					(String)mm.get("status_name")
					);
			
			list.add(status);
		}
		return list;
	}
	
	/**
	 * 获取所有跟进方式
	 * @return
	 * @throws SQLException
	 */
	public List<Way> getAllWay() throws SQLException{
		String sql = "select * from way";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<Way> list = new ArrayList<>();
		for (Map<String,Object> mm : map) {
			Way way = new Way();
			way.setWay_id(Integer.parseInt(mm.get("way_id").toString()));
			way.setWay_name((String)mm.get("way_name"));
			
			list.add(way);
		}
		return list;
	}
	
}
